package com.busdepot.webapp.domain;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Component;

import com.busdepot.webapp.util.DbUtils;


@Component("customerRepository")
public class CustomerRepositoryImpl implements CustomerRepository {
	private SimpleJdbcTemplate simpleJdbcTemplate = DbUtils
			.getSimpleJdbcTemplate();
	
	public List<Customer> getCustomerByUsername(String username) {
		String sql = "select * from customer where username=?";
	    return this.simpleJdbcTemplate.query(sql, new CustomerMapper(), username);
	}

	public void save(Customer customer) {
		List<Customer> customerList = getCustomerByUsername(customer.getUsername());
		if (customerList.size() == 0) {
			insert(customer);
		} else {
			update(customer);
		}

	}

	private void update(Customer customer) {
		String sql = "update customer set password=?, personal_Firstname=?, personal_Lastname=?, personal_Email=?, personal_Telarea=?, personal_Telpre=?, personal_Telsuf=?, personal_Street1=?, personal_Street2=?, personal_City=?, personal_State=?, personal_Zip=?, personal_Country=?, shipping_Firstname=?, shipping_Lastname=?, shipping_email=?, shipping_Telarea=?, shipping_Telpre=?, shipping_Telsuf=?, shipping_Street1=?, shipping_Street2=?, shipping_City=?, shipping_State=?, shipping_Zip=?, shipping_Country=?, shipping_method=?, comment=?, personal_field=?, model=?, backordered=? where username=?";
		simpleJdbcTemplate
				.update(sql, customer.getPassword(), customer.getFirstName(),
						customer.getLastName(), customer.getEmail(), customer
								.getAreaCode(), customer.getExchange(),
						customer.getNumber(), customer.getAddress1(), customer
								.getAddress2(), customer.getCity(), customer
								.getState(), customer.getZip(), customer
								.getCountry(), customer.getShippingFirstName(),
						customer.getShippingLastName(), customer
								.getShippingEmail(), customer
								.getShippingAreaCode(), customer
								.getShippingExchange(), customer
								.getShippingNumber(), customer
								.getShippingAddress1(), customer
								.getShippingAddress2(), customer
								.getShippingCity(),
						customer.getShippingState(), customer.getShippingZip(),
						customer.getShippingCountry(), customer
								.getShippingPreference(), customer
								.getCommentPreference(), customer
								.getPersonalMemo(), customer.getVehicleModel(),
						customer.getBackorderedPreference(), customer.getUsername());

	}

	private void insert(Customer customer) {
		String sql = "insert into customer (username, password, personal_Firstname, personal_Lastname, personal_Email, personal_Telarea, personal_Telpre, personal_Telsuf, personal_Street1, personal_Street2, personal_City, personal_State, personal_Zip, personal_Country, shipping_Firstname, shipping_Lastname, shipping_email, shipping_Telarea, shipping_Telpre, shipping_Telsuf, shipping_Street1, shipping_Street2, shipping_City, shipping_State, shipping_Zip, shipping_Country, shipping_method, comment, personal_field, model, backordered) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?)";
		simpleJdbcTemplate
				.update(sql, customer.getUsername(), customer.getPassword(),
						customer.getFirstName(), customer.getLastName(),
						customer.getEmail(), customer.getAreaCode(), customer
								.getExchange(), customer.getNumber(), customer
								.getAddress1(), customer.getAddress2(),
						customer.getCity(), customer.getState(), customer
								.getZip(), customer.getCountry(), customer
								.getShippingFirstName(), customer
								.getShippingLastName(), customer
								.getShippingEmail(), customer
								.getShippingAreaCode(), customer
								.getShippingExchange(), customer
								.getShippingNumber(), customer
								.getShippingAddress1(), customer
								.getShippingAddress2(), customer
								.getShippingCity(),
						customer.getShippingState(), customer.getShippingZip(),
						customer.getShippingCountry(), customer
								.getShippingPreference(), customer
								.getCommentPreference(), customer
								.getPersonalMemo(), customer.getVehicleModel(),
						customer.getBackorderedPreference());
	}

	
	private static final class CustomerMapper implements RowMapper {
			public Customer mapRow (ResultSet rs, int rowNum) throws SQLException {
				String username = rs.getString("username");
				String password = rs.getString("password");
				String firstName = rs.getString("personal_Firstname");
				String lastName = rs.getString("personal_Lastname");
				String email = rs.getString("personal_Email");
				int areaCode = rs.getInt("personal_Telarea");
				int exchange = rs.getInt("personal_Telpre");
				int number = rs.getInt("personal_Telsuf");
				String address1 = rs.getString("personal_Street1");
				String address2 = rs.getString("personal_Street2");
				String city = rs.getString("personal_City");
				String state = rs.getString("personal_State");
				int zip = rs.getInt("personal_Zip");
				String country = rs.getString("personal_Country");
				String shippingFirstName = rs.getString("shipping_Firstname");
				String shippingLastName = rs.getString("shipping_Lastname");
				String shippingEmail = rs.getString("shipping_email");
				int shippingAreaCode = rs.getInt("shipping_Telarea");
				int shippingExchange = rs.getInt("shipping_Telpre");
				int shippingNumber = rs.getInt("shipping_Telsuf");
				String shippingAddress1 = rs.getString("shipping_Street1");
				String shippingAddress2 = rs.getString("shipping_Street2");
				String shippingCity = rs.getString("shipping_City"); 
				String shippingState = rs.getString("shipping_State");
				int shippingZip = rs.getInt("shipping_Zip");
				String shippingCountry = rs.getString("shipping_Country"); 
				String shippingPreference = rs.getString("shipping_method");
				String commentPreference = rs.getString("comment"); 
				String personalMemo = rs.getString("personal_field");
				String vehicleModel = rs.getString("model");
				String backorderedPreference = rs.getString("backordered");
				
				Customer customer = new 	Customer(username, password, firstName, lastName, email, areaCode, exchange, number, address1, address2, city, state, zip, country, shippingFirstName, shippingLastName, shippingEmail, shippingAreaCode, shippingExchange, shippingNumber, shippingAddress1, shippingAddress2, shippingCity, shippingState, shippingZip, shippingCountry, commentPreference,shippingPreference, backorderedPreference, vehicleModel, personalMemo);
				return customer;
	        }					     	
		   }; 
}
